
package com.feib.soeasy.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.feib.soeasy.dao.Soez5001Dao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.pagination.PaginatedList;


/**
 * @title (#)Soez5001JdbcDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/7 create by Jimmy Liu<br>
 */
@Repository("soez5001Dao")
public class Soez5001JdbcDao extends JdbcDaoSupport implements Soez5001Dao {

    @Resource(name = "dataSource")
    public void setMyDataSource(DataSource dataSource) {
        super.setDataSource(dataSource); 
    }   



    /**
     * 返回異常繳款明細表項目
     * 
     * @param groupNo
     * @param payerNo
     * @return
     */
    //    public List<Map<String, Object>> queryByGroupNo(String groupNo)
    //    {
    //        StringBuffer sql = new StringBuffer();
    //        List<Object> args = new ArrayList<Object>();
    //        sql.append("select * from FEEDBACK_LOG a left join payment b on a.USERNO = b.VIRTUAL_ACCOUNT  ");
    //       // if (groupNo=="000000000001"){
    //          sql.append(" inner join REAL_ACCOUNT c on c.account_No = a.MRKEY join APP_GROUP g on c.group_pk = g.group_pk and g.group_no = ?");
    //       // }
    //        sql.append(" where a.FBCODE=2");//去除 銷帳結果    (1)已處理
    //        
    //        args.add(groupNo);
    //        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
    //       // return this.getJdbcTemplate().queryForList(sql.toString());
    //        
    //    }

    public Map<String, Object> findPaymentByPk(Long paymentPk)
    {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        sql.append("select * from PAYMENT WHERE PAYMENT_PK = ? ");
        args.add(paymentPk);
        return this.getJdbcTemplate().queryForMap(sql.toString(), args.toArray());
    }
    
    @Override
    public Map<String, Object> findFeedbackLogByPk(Long feedbackUid) {
                
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        sql.append("SELECT A.* FROM FEEDBACK_LOG A WHERE A.UID = ? ");
        args.add(feedbackUid);
        return this.getJdbcTemplate().queryForMap(sql.toString(), args.toArray());
    }

    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.Soez5001Dao#findFeedbackLogByPk(java.lang.Long, com.feib.soeasy.model.Group)
     */
    @Override
    public Map<String, Object> findFeedbackLogByPk(Long feedbackUid, Group group) {
        
        Long groupPk = null == group ? null : group.getGroupPk();
        
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        sql.append("SELECT A.* FROM FEEDBACK_LOG A WHERE A.UID = ? ");
        args.add(feedbackUid);
        Map<String, Object> result = this.getJdbcTemplate().queryForMap(sql.toString(), args.toArray());
        
        if (null != result)
        {
            result.put("FEEDBACK_CHANNEL", result.get("CHANNEL"));
            
            String feedbackType = String.valueOf(result.get("FEEDBACK_TYPE"));
            
            args.clear();
            
            StringBuffer paymentSql = new StringBuffer();
            paymentSql.append(" SELECT P.PAYMENT_PK, P.GROUP_PK,  P.WRITE_OFF_PAYMENT_PK, P.PAYMENT_NAME");
            paymentSql.append(" , P.VIRTUAL_ACCOUNT, P.PAYMENT_STATUS, P.PAYER_ID, P.PAYER_NAME, P.STUDENT_ID, P.PAY_DUE");
            paymentSql.append(" , P.TOTAL_AMOUNT, P.TOTAL_LOANABLE_AMOUNT, P.BARCODE_STORES_2");
            paymentSql.append(" , G.GROUP_NO, G.GROUP_NAME, GROUP_TYPE");
            paymentSql.append(" FROM PAYMENT P ");
            paymentSql.append(" JOIN APP_GROUP G ON P.GROUP_PK = G.GROUP_PK");
            if ("2".equals(feedbackType))
            {
                // 超商類
                paymentSql.append(" WHERE BARCODE_STORES_2 = ? ");
                args.add(result.get("BARCDOE2"));
            }
            else
            {
                // 銀行通路
                paymentSql.append(" WHERE VIRTUAL_ACCOUNT = ? ");
                String va ="";
                if (null != result.get("USERNO"))
                {
                    va = (String)result.get("USERNO");
                    va = va.trim();
                }
                args.add(va);
            }            
            
            if (null != groupPk)
            {
                paymentSql.append(" AND G.GROUP_PK = ? ");
                args.add(groupPk);
            }
            
            result.putAll(this.getJdbcTemplate().queryForMap(paymentSql.toString(), args.toArray()));
            
            
        }
        
        return result;
    }
    
    public String getQuerySql(Group group, List<Object> args)
    {
        String groupNo = null == group ? null : group.getGroupNo();
        //黃郁珊 依中租需求修改
        StringBuffer paymentSql = new StringBuffer();
//        paymentSql.append(" SELECT P.PAYMENT_PK, P.GROUP_PK,  P.WRITE_OFF_PAYMENT_PK, P.PAYMENT_NAME");
//        paymentSql.append(" , P.VIRTUAL_ACCOUNT, P.PAYMENT_STATUS, P.PAYER_ID, P.PAYER_NAME, P.STUDENT_ID, P.PAY_DUE");
//        paymentSql.append(" , P.TOTAL_AMOUNT, P.TOTAL_LOANABLE_AMOUNT, P.BARCODE_STORES_2");
//        //paymentSql.append(" , W.AMOUNT AS WRITE_OF_AMOUNT, W.CHANNEL, W.TX_DATE, W.WRITE_OFF_DATE");
//        paymentSql.append(" , G.GROUP_NO, G.GROUP_NAME, GROUP_TYPE");
//        //paymentSql.append(" FROM PAYMENT P LEFT  JOIN  WRITE_OFF_PAYMENT W ON P.WRITE_OFF_PAYMENT_PK = W.WRITE_OFF_PAYMENT_PK");
//        paymentSql.append(" FROM PAYMENT P ");
//        paymentSql.append(" JOIN APP_GROUP G ON P.GROUP_PK = G.GROUP_PK");
        
        paymentSql.append("SELECT G.GROUP_PK ,G.GROUP_NO, G.GROUP_NAME, R.VIRTUAL_ACCOUNT_NO ");
        paymentSql.append(",LEN(R.VIRTUAL_ACCOUNT_NO) AS length FROM REAL_ACCOUNT R  JOIN APP_GROUP G ON ");
        paymentSql.append("R.GROUP_PK = G.GROUP_PK ");
        if (StringUtils.isNotEmpty(groupNo))
            paymentSql.append(" WHERE G.GROUP_NO = ? ");

        StringBuffer feedbackSelectFields = new StringBuffer();
        feedbackSelectFields.append("SELECT A.UID, A. FBFILENAME, A.FBTIME, A.ADAY, A.ATIME");
        feedbackSelectFields.append(", A.ERR_MSG, A.CHANNEL AS FEEDBACK_CHANNEL, A.TXAMT, A.USERNO");


        StringBuffer sql = new StringBuffer();
        args.clear();
        sql.append(feedbackSelectFields.toString());
        sql.append(" , B.*,P.PAYMENT_PK, P.GROUP_PK, "+
                 "P.WRITE_OFF_PAYMENT_PK, P.PAYMENT_NAME , P.VIRTUAL_ACCOUNT, P.PAYMENT_STATUS, "+
                 "P.PAYER_ID, P.PAYER_NAME, P.STUDENT_ID, P.PAY_DUE , P.TOTAL_AMOUNT, "+
                 "P.TOTAL_LOANABLE_AMOUNT, P.BARCODE_STORES_2 ");
        sql.append(" FROM FEEDBACK_LOG A LEFT JOIN (");
        sql.append(paymentSql.toString());
        //sql.append(" ) B ON A.BARCDOE2 = B.BARCODE_STORES_2");
        sql.append(" ) B ON left(RTRIM(A.USERNO),B.length) = B.VIRTUAL_ACCOUNT_NO ");
        sql.append("LEFT JOIN PAYMENT P  ON A.BARCDOE2 = P.BARCODE_STORES_2 ");
        sql.append(" WHERE A.FEEDBACK_TYPE = 2 "); // third party, ex:超商
        sql.append(" AND A.FBCODE='2'"); // 有問題的
        if (StringUtils.isNotEmpty(groupNo))
        {
            sql.append(" AND B.GROUP_NO = ?");
            args.add(groupNo);
            args.add(groupNo);
        }

        sql.append(" union ");
        sql.append(feedbackSelectFields.toString());
        sql.append(" , B.*,P.PAYMENT_PK, P.GROUP_PK, "+
                "P.WRITE_OFF_PAYMENT_PK, P.PAYMENT_NAME , P.VIRTUAL_ACCOUNT, P.PAYMENT_STATUS, "+
                "P.PAYER_ID, P.PAYER_NAME, P.STUDENT_ID, P.PAY_DUE , P.TOTAL_AMOUNT, "+
                "P.TOTAL_LOANABLE_AMOUNT, P.BARCODE_STORES_2 ");
        sql.append(" FROM FEEDBACK_LOG A LEFT JOIN (");
        sql.append(paymentSql.toString());
       // sql.append(" ) B  ON RTRIM(A.USERNO) = B.VIRTUAL_ACCOUNT");
        sql.append(" ) B ON left(RTRIM(A.USERNO),B.length) = B.VIRTUAL_ACCOUNT_NO  ");
        sql.append("LEFT JOIN PAYMENT P   ON RTRIM(A.USERNO) = P.VIRTUAL_ACCOUNT");
        sql.append(" WHERE A.FEEDBACK_TYPE = 1 "); // 銀行通路
        sql.append(" AND A.FBCODE='2'"); // 有問題的
        if (StringUtils.isNotEmpty(groupNo))
        {
            sql.append(" AND B.GROUP_NO = ?");
            args.add(groupNo);
            args.add(groupNo);
        }

        logger.debug("getQuerySql SQL="+sql+"; args="+args);
        return sql.toString();
    }

    /**
     * 返回異常繳款明細表項目
     * 
     * @param groupNo
     * @param payerNo
     * @return
     */
    public PaginatedList<Map<String, Object>> queryByGroup(Group group, PaginatedList<Map<String, Object>> paginationConfig)
    {
        List<Object> args = new ArrayList<Object>();
        String sql = getQuerySql(group, args);
        logger.debug("queryByGroupNo SQL="+sql+"; args="+args);
        
        RowMapper<Map<String, Object>> rowMapper = new ColumnMapRowMapper();
        PaginatedList<Map<String, Object>> result = new PaginatedList<Map<String,Object>>();
        result.setPageSize(paginationConfig.getPageSize());
        result.setPageNumber(paginationConfig.getPageNumber());
        result.setSortCriterion(paginationConfig.getSortCriterion());
        result.setSortDirection(paginationConfig.getSortDirection());
        
        RowMapperResultSetExtractor<Map<String, Object>> extracotr = new RowMapperResultSetExtractor<Map<String, Object>>(rowMapper, result);
        this.getJdbcTemplate().query(sql.toString(), args.toArray(), extracotr);
        
        return extracotr.getPaginatedList();

    }

    class RowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>>
    {
        private RowMapper<T> rowMapper;
        private PaginatedList<T> paginatedList;
        
        public RowMapperResultSetExtractor(RowMapper<T> rowMapper, PaginatedList<T> paginatedList) {
            Assert.notNull(rowMapper, "RowMapper is required");
            Assert.notNull(paginatedList, "PaginatedList is required");
            this.rowMapper = rowMapper;
            this.paginatedList = paginatedList;
        }
        
        
        public List<T> extractData(ResultSet rs) throws SQLException {
            List<T> results = new ArrayList<T>();

            int maxResults = paginatedList.getPageSize(); //the maximum number of results
            int firstResult = paginatedList.getFirstIndexForCurrentPage();  //the first result to retrieve, numbered from 0
            int lastResult = firstResult + maxResults -1;  //the first result to retrieve, numbered from 0

            int rowNum = 0;
            while (rs.next()) {
                
                if (firstResult <= rowNum && lastResult >= rowNum)
                    results.add(this.rowMapper.mapRow(rs, rowNum++));
                else
                    rowNum++;
            }
            
            paginatedList.setFullListSize(rowNum);
            paginatedList.setPaginatedList(results);
            return results;

        }


        
        /**
         * @return the paginatedList
         */
        public PaginatedList<T> getPaginatedList() {
            return paginatedList;
        }
    }


    /* (non-Javadoc)
     * @see com.feib.soeasy.dao.Soez5001Dao#queryByUSERNO(java.lang.String)
     */
    /**
     * 依照  序號 查詢該筆資訊
     */
    @Override
    public List<Map<String, Object>> queryByUID(String groupNo,String UID) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        sql.append("select * from FEEDBACK_LOG a left join payment b on a.USERNO = b.VIRTUAL_ACCOUNT  ");
        // if (groupNo=="000000000001"){
        sql.append(" inner join REAL_ACCOUNT c on c.account_No = a.MRKEY join APP_GROUP g on c.group_pk = g.group_pk and g.group_no = ?");
        // }
        sql.append(" where a.FBCODE=2  and  a.UID= ? ");//去除 銷帳結果    (1)已處理

        args.add(groupNo);
        args.add(UID);
        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());

    }



    @Override
    public int updateFeedbackLogResultCode(Integer fbcode, Long uid, String reason) {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();

        sql.append("update FEEDBACK_LOG SET FBCODE =?, REASON_NOTE = ? WHERE UID=? ");
        
        args.add(fbcode);
        args.add(reason);
        args.add(uid);
        
        logger.debug("updateFeedbackLogResultCode SQL="+sql +"; args=" + args);

        return this.getJdbcTemplate().update(sql.toString(), args.toArray());

    }



}
